# -*- coding: utf-8 -*-

from passlib.context import CryptContext
from collections import defaultdict
import time
import copy
import xlwt


class GenerateExcel(object):
    def __init__(self, name_or_stream, sheet_names=['sheet'], encoding='utf8'):
        self.name_or_stream = name_or_stream
        self.encoding = encoding
        self.wb = xlwt.Workbook(encoding=self.encoding)
        self.sheets = {} # {'sheet_name': sheet}
        self.create_sheets(sheet_names)

    @classmethod
    def get_alignment(cls, position):
        postions = {
            'center': xlwt.Alignment.HORZ_CENTER,
            'left': xlwt.Alignment.HORZ_LEFT
        }
        style = xlwt.XFStyle()
        alignment = xlwt.Alignment()
        alignment.horz = postions[position]
        style.alignment = alignment
        return style

    @classmethod
    def get_cell_default_style(cls):
        style = cls.get_alignment('left')
        style.num_format_str = '0.00'
        return style

    def create_sheets(self, sheet_names):
        for sheet_name in sheet_names:
            # cell_overwrite_ok:False(默认) 重复写入一个cell会报错
            sheet = self.wb.add_sheet(sheet_name)
            self.sheets.update({sheet_name: sheet})

    def get_sheets(self, sheet_names):
        sheets = []
        if sheet_names:
            # sheets = [self.sheets[n] for n in sheet_names if self.sheets.get(n, None)]
            sheets = [self.sheets[n] for n in sheet_names]
        else:
            sheets = self.sheets.values()
        return sheets

    def write_headers(self, cols, sheet_names=[], style=None, start_row=0):
        # cols: [(name, width), ....]
        sheets = self.get_sheets(sheet_names)
        if not style:
            style = self.get_alignment('center')

        for sheet in sheets:
            for col_index, col in enumerate(cols):
                col_name, col_width = col
                sheet.write(start_row, col_index, col_name, style)
                sheet.col(col_index).width = col_width

    def write_sheets_rows(self, sheets_rows, style=None, start_row=1):
        # 向sheet写入数据
        # sheets_rows: {sheet_name_1: [[row1], [row2], [row3]], sheet_name_2:[]...}
        for sheet_name, rows in sheets_rows.iteritems():
            # 如果sheet name不存在抛出keyerror，使用get(name, None)会隐藏错误
            row_index = start_row
            for row in rows:
                for col_index, val in enumerate(row):
                    self.write_cell(row_index, col_index, val, [sheet_name], style)
                row_index += 1

    def write_cell(self, row, col, val, sheet_names=[], style=None):
        sheets = self.get_sheets(sheet_names)
        if not style:
            style = self.get_cell_default_style()
        for sheet in sheets:
            sheet.write(row, col, val, style)

    def write_cell_formula(self, infos, style=None):
        # infos: {'sheet':[(row,col,method)...]}
        for sheet_name, pos_methods in infos.iteritems():
            for row, col, method in pos_methods:
                self.write_cell(row, col, xlwt.Formula(method), [sheet_name], style)

    def save(self):
        self.wb.save(self.name_or_stream)


if __name__ == '__main__':
    headers = [
        ('日期', 3333),
        ('客户', 3343),
        ('品名', 3353),
        ('工艺类型', 4563),
        ('图案', 3373),
        ('匹数', 3000),
        ('结算方式', 3393),
        ('重量(Kg)', 3000),
        ('金额(元)', 3000)
    ]
    sheets_rows = {
        '一月': [
            ['2018-03-12', '徐志雷', '丝绸', '剪毛,水洗', '狮子', 12, '按毛重', 13.22, 278.12],
            ['2018-03-13', '李志刚', '尼龙', '烫光,剪毛,水洗', '兔子', 12, '按毛重', 15.22, 218.13],
            ['2018-03-14', '刘安', '棉', '开幅,剪毛,水洗', '老鹰', 12, '按净重', 11.22, 168.12]
        ],
        '二月': [
            ['2018-03-12', '徐志雷', '丝绸', '剪毛,水洗', '狮子', 12, '按毛重', 13.22, 278.12],
            ['2018-03-13', '李志刚', '尼龙', '烫光,剪毛,水洗', '兔子', 12, '按毛重', 15.22, 218.13],
            ['2018-03-14', '刘安', '棉', '开幅,剪毛,水洗', '老鹰', 12, '按净重', 11.22, 168.12]
        ]
    }
    excel_obj = GenerateExcel('爱贝儿2018年1到3月对账单.xls')
    excel_obj.create_sheets(['一月', '二月', '三月'])
    # 日期 客户 品名 工艺类型 图案 匹数 结算方式 重量(Kg) 金额(元)
    excel_obj.write_headers(headers)
    excel_obj.write_sheets_rows(sheets_rows)
    excel_obj.save()
